﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using EShop.Common;
using EShop.BussinessObjects;
using MySql.Data.MySqlClient;

namespace EShop.DataAccess
{
    public class CDACompanyWeeklyMenu
    {
        public static List<CompanyWeeklyMenu> Select(CompanyWeeklyMenu companyWeeklyMenu)
        {
            MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
            string NotEffective = ((int)cnstDatabase.DBOperationResult.ValueIsNotEffective).ToString();
            con.Open();
            string _cmdText =
                            "SELECT c.*, p.Name ProductName, p.Price ProductPrice " +
                            "FROM companyweeklymenu c " +
                            "LEFT JOIN Product p ON c.ProductID = p.ID " +
                            "WHERE ((c.ID = " + companyWeeklyMenu.ID.ToString() + ") OR (" + companyWeeklyMenu.ID.ToString() + " = " + NotEffective + "))" +
                            "  AND ((c.CompanyID=" + companyWeeklyMenu.Company.ID.ToString() + ")OR(" + companyWeeklyMenu.Company.ID.ToString() + "=" + NotEffective + "))" +
                            "  AND ((c.DayOfWeek=" + companyWeeklyMenu.DayOfWeek.ToString() + ")OR(" + companyWeeklyMenu.DayOfWeek.ToString() + "=" + NotEffective + "))" +
                            "ORDER BY c.DayOfWeek, c.Sequence";

            MySqlCommand cmd = new MySqlCommand(_cmdText, con);
            MySqlDataAdapter dataAdapter = new MySqlDataAdapter(cmd);
            DataTable tbl = new DataTable();
            dataAdapter.Fill(tbl);

            List<CompanyWeeklyMenu> companyWeeklyMenuList = new List<CompanyWeeklyMenu>();

            foreach (DataRow row in tbl.Rows)
            {
                CompanyWeeklyMenu _companyWeeklyMenu = new CompanyWeeklyMenu();
                _companyWeeklyMenu.ID = (row["ID"].ToString() == "" ? ((int)cnstDatabase.DBOperationResult.NullValue) : Convert.ToInt32(row["ID"]));

                _companyWeeklyMenu.Company = new Company();
                _companyWeeklyMenu.Company.ID = (row["CompanyID"].ToString() == "" ? ((int)cnstDatabase.DBOperationResult.NullValue) : Convert.ToInt32(row["CompanyID"]));

                _companyWeeklyMenu.DayOfWeek = (row["DayOfWeek"].ToString() == "" ? ((int)cnstDatabase.DBOperationResult.NullValue) : Convert.ToInt32(row["DayOfWeek"].ToString()));
                _companyWeeklyMenu.Sequence = (row["Sequence"].ToString() == "" ? ((int)cnstDatabase.DBOperationResult.NullValue) : Convert.ToInt32(row["Sequence"].ToString()));

                _companyWeeklyMenu.Product = new Product();
                _companyWeeklyMenu.Product.ID = (row["ProductID"].ToString() == "" ? ((int)cnstDatabase.DBOperationResult.NullValue) : Convert.ToInt32(row["ProductID"]));
                _companyWeeklyMenu.Product.Name = (row["ProductName"].ToString() == "" ? ((int)cnstDatabase.DBOperationResult.NullValue).ToString() : row["ProductName"].ToString());
                _companyWeeklyMenu.Product.Price = (row["ProductPrice"].ToString() == "" ? ((int)cnstDatabase.DBOperationResult.NullValue) : Convert.ToDecimal(row["ProductPrice"]));
                
                companyWeeklyMenuList.Add(_companyWeeklyMenu);
            }
            con.Close();
            return companyWeeklyMenuList;

        }
    }
}